﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data;
using System.Data.Objects.DataClasses;
using System.Data.Objects;
using System.Data.Metadata.Edm;

namespace eGrocery.BaseDAL
{
    public class DBHelper
    {
        #region private members
        private string _connectionstring = "";
        private DbConnection _connection;
        private DbCommand _command;
        private DbProviderFactory _factory = null;
        #endregion

        #region properties

        /// <summary>
        /// Gets or Sets the connection string for the database
        /// </summary>
        public string connectionstring
        {
            get
            {
                return _connectionstring;
            }
            set
            {
                if (value != "")
                {
                    _connectionstring = value;
                }
            }
        }

        /// <summary>
        /// Gets the connection object for the database
        /// </summary>
        public DbConnection connection
        {
            get
            {
                return _connection;
            }
        }

        /// <summary>
        /// Gets the command object for the database
        /// </summary>
        public DbCommand command
        {
            get
            {
                return _command;
            }
        }

        #endregion

        # region methods

        /// <summary>
        /// Determines the correct provider to use and sets up the connection and command
        /// objects for use in other methods
        /// </summary>
        /// <param name="connectString">The full connection string to the database</param>
        /// <param name="providerlist">The enum value of providers from dbutilities.Providers</param>
        public void CreateDBObjects(string connectString, DataBaseType providerList)
        {
            //CreateDBObjects(connectString, providerList, null);
            switch (providerList)
            {
                case DataBaseType.SqlServer:
                    _factory = SqlClientFactory.Instance;
                    break;
                case DataBaseType.Oracle:
                    _factory = OracleClientFactory.Instance;
                    break;
                case DataBaseType.OleDB:
                    _factory = OleDbFactory.Instance;
                    break;
                case DataBaseType.ODBC:
                    _factory = OdbcFactory.Instance;
                    break;
            }

            _connection = _factory.CreateConnection();
            _command = _factory.CreateCommand();

            _connection.ConnectionString = connectString;
            _command.Connection = connection;
        }

        #region parameters

        /// <summary>
        /// Creates a parameter and adds it to the command object
        /// </summary>
        /// <param name="name">The parameter name</param>
        /// <param name="value">The paremeter value</param>
        /// <returns></returns>
        public int AddParameter(string name, object value)
        {
            DbParameter parm = _factory.CreateParameter();
            parm.ParameterName = name;
            parm.Value = value;
            return command.Parameters.Add(parm);
        }

        /// <summary>
        /// Creates a parameter and adds it to the command object
        /// </summary>
        /// <param name="parameter">A parameter object</param>
        /// <returns></returns>
        public int AddParameter(DbParameter parameter)
        {
            return command.Parameters.Add(parameter);
        }

        #endregion

        #region transactions

        /// <summary>
        /// Starts a transaction for the command object
        /// </summary>
        private void BeginTransaction()
        {
            if (connection.State == System.Data.ConnectionState.Closed)
            {
                connection.Open();
            }
            command.Transaction = connection.BeginTransaction();
        }

        /// <summary>
        /// Commits a transaction for the command object
        /// </summary>
        private void CommitTransaction()
        {
            if (command.Transaction != null)
                command.Transaction.Commit();
            connection.Close();
        }

        /// <summary>
        /// Rolls back the transaction for the command object
        /// </summary>
        private void RollbackTransaction()
        {
            command.Transaction.Rollback();
            connection.Close();
        }

        #endregion

        #region execute database functions

        /// <summary>
        /// Executes a statement that does not return a result set, such as an INSERT, UPDATE, DELETE, or a data definition statement
        /// </summary>
        /// <param name="query">The query, either SQL or Procedures</param>
        /// <param name="commandtype">The command type, text, storedprocedure, or tabledirect</param>
        /// <param name="connectionstate">The connection state</param>
        /// <returns>An integer value</returns>
        public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            command.CommandText = query;
            command.CommandType = commandtype;
            int i = -1;
            try
            {
                if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }

                BeginTransaction();

                i = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                RollbackTransaction();
                throw (ex);
            }
            finally
            {
                CommitTransaction();
                command.Parameters.Clear();

                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                    //command.Dispose();
                }
            }

            return i;
        }

        /// <summary>
        /// Executes a statement that returns a single value. 
        /// If this method is called on a query that returns multiple rows and columns, only the first column of the first row is returned.
        /// </summary>
        /// <param name="query">The query, either SQL or Procedures</param>
        /// <param name="commandtype">The command type, text, storedprocedure, or tabledirect</param>
        /// <param name="connectionstate">The connection state</param>
        /// <returns>An object that holds the return value(s) from the query</returns>
        public object ExecuteScaler(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            command.CommandText = query;
            command.CommandType = commandtype;
            object obj = null;
            try
            {
                if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }

                BeginTransaction();
                obj = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                RollbackTransaction();
                throw (ex);
            }
            finally
            {
                CommitTransaction();
                command.Parameters.Clear();

                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                    command.Dispose();
                }
            }

            return obj;
        }

        /// <summary>
        /// Executes a SQL statement that returns a result set.
        /// </summary>
        /// <param name="query">The query, either SQL or Procedures</param>
        /// <param name="commandtype">The command type, text, storedprocedure, or tabledirect</param>
        /// <param name="connectionstate">The connection state</param>
        /// <returns>A datareader object</returns>
        public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            command.CommandText = query;
            command.CommandType = commandtype;
            DbDataReader reader = null;
            try
            {
                if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                if (connectionstate == System.Data.ConnectionState.Open)
                {
                    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = command.ExecuteReader();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                command.Parameters.Clear();
            }

            return reader;
        }

        /// <summary>
        /// Generates a dataset
        /// </summary>
        /// <param name="query">The query, either SQL or Procedures</param>
        /// <param name="commandtype">The command type, text, storedprocedure, or tabledirect</param>
        /// <param name="connectionstate">The connection state</param>
        /// <returns>A dataset containing data from the database</returns>
        public DataSet GetDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            command.CommandText = query;
            command.CommandType = commandtype;
            adapter.SelectCommand = command;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                command.Parameters.Clear();

                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                    command.Dispose();
                }
            }
            return ds;
        }

        #endregion

        #endregion

        #region enums

        /// <summary>
        /// A list of data providers
        /// </summary>
        public enum DataBaseType
        {
            SqlServer,
            OleDB,
            ODBC,
            Oracle,
            Null
        }

        #endregion

    }
    public static class Helper
    {
        /// <summary>
        /// Save the reocrd to related table, if the primary key passed update it else add it.
        /// </summary>
        /// <param name="objEntity">Entity Object</param>
        public static void Save(this EntityObject objEntity)
        {
            try                                       // Update record.
            {
                    //Global.Context.Cities.Add(               
            }
            catch (OptimisticConcurrencyException)   // Insert if found the record is already exists.
            {
               // Global.Context.DetachObject(objEntity);
                //Global.Context.AddObject(Global.Context.GetEntitySetName(objEntity), objEntity);
                //Global.Context.SaveChanges();
            }
        }

        ///// <summary>
        ///// Delete the record from related table. The primary key value must be filled.
        ///// </summary>
        ///// <param name="objEntity">Entity Object</param>
        //public static void Delete(this EntityObject objEntity)
        //{
        //    if (objEntity != null)
        //    {
        //        Global.Context.DetachObject(objEntity);
        //        Global.Context.Attach(objEntity);
        //        Global.Context.ObjectStateManager.ChangeObjectState(objEntity, EntityState.Deleted);
        //        Global.Context.SaveChanges();
        //    }
        //}

        private static string GetEntitySetName(this ObjectContext Context, EntityObject entity)
        {
            string entityTypeName = entity.GetType().Name;
            var container = Context.MetadataWorkspace.GetEntityContainer(Context.DefaultContainerName, DataSpace.CSpace);
            return container.BaseEntitySets.FirstOrDefault(meta => meta.ElementType.Name == entityTypeName).Name;
        }

        private static void DetachObject(this ObjectContext Context, EntityObject entity)
        {
            if (entity.EntityKey != null)
            {
                object objentity = null;
                var exist = Context.TryGetObjectByKey(entity.EntityKey, out objentity);
                if (exist) { Context.Detach(entity); }
            }
        }
    }

}
